package com.jomoo.card.dao.impl;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.jomoo.card.dao.CardDao;
import com.jomoo.common.pojo.JoMooCard;
import com.jomoo.common.pojo.JoMooSpec;
import com.jomoo.common.utils.Constants;


/**
 * 
* @ClassName: CardDaoImpl 
* @Description: 
* @author sddm.haosc
* @date 2017年12月12日 上午10:04:58 
*
 */
@Repository
@Transactional(rollbackFor = Exception.class)
public class CardDaoImpl implements CardDao
{

	@Resource(name = "sessionFactory")
	private SessionFactory sessionFactory;
	
	/**
	 * 获取产品价格
	 * 
	 * @return
	 * @throws Exception
	 */
	@Override
	public Float getSecondCity(Integer iSPecid) throws Exception
	{
		Session session = sessionFactory.openSession();
		
		Float fsale = null;
		try
		{
			StringBuilder hql = new StringBuilder();
			
			hql.append(" select new JoMooSpec(js.sale) from JoMooSpec js where js.active=1 and js.id=:specId ");

			Query query = session.createQuery(hql.toString());

			query.setInteger("specId", iSPecid);

			JoMooSpec fSale = (JoMooSpec) query.uniqueResult();

			fsale = fSale.getSale();
		}
		catch (Exception e)
		{
			e.printStackTrace();
			throw e;
		}
		finally
		{
			if (null != session)
			{
				session.close();
			}
			if (null != sessionFactory)
			{
				sessionFactory.close();
			}
		}
		return fsale;
	}

	/**
	 * 获取一级城市名
	 * 
	 * @return
	 * @throws Exception
	 */
	@Override
	public String getFirstCityName(Integer iFCityId) throws Exception
	{
		Session session = sessionFactory.openSession();
		
		String fname = null;
		
		try
		{
			StringBuilder hql = new StringBuilder();
			
			hql.append(" select jc.cityName from JoMooCity jc where jc.active=1 and jc.id=:fid ");

			Query query = session.createQuery(hql.toString());

			query.setInteger("fid", iFCityId);

			fname = (String) query.uniqueResult();
		}
		catch (Exception e)
		{
			e.printStackTrace();
			throw e;
		}
		finally
		{
			if (null != session)
			{
				session.close();
			}
			if (null != sessionFactory)
			{
				sessionFactory.close();
			}
		}
		return fname;
	}

	/**
	 * 持久化任意对象
	 */
	@Override
	public Integer saveAllObj(Object obj) throws Exception
	{
		Session session = sessionFactory.openSession();
		
		Integer iId = null;
		
		try
		{
			iId = (Integer) session.save(obj);
		}
		catch (Exception e)
		{
			throw e;
		}
		finally
		{
			if (null != session)
			{
				session.close();
			}
			if (null != sessionFactory)
			{
				sessionFactory.close();
			}
		}
		return iId;
	}

	/**
	 * 查询服务卡
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	@Override
	public List<JoMooCard> getServiceCards(String strPopName, String strPopPhone,
			Integer iCustomerId, Integer iSetupType, Integer iSetupState,
			Integer iProductType, Integer iProductSpec, String strBeginTime,
			Date strEndTime) throws Exception
	{
		Session session = sessionFactory.openSession();
		
		List cardList = null;

		SimpleDateFormat simp = new SimpleDateFormat("yyyy-MM-dd");
		try
		{
			StringBuilder hql = new StringBuilder();
			hql.append(" select new JoMooCard(jc.id, jc.popName, jt.typeName, js.specName, jc.setupTime, jc.remark, jst.statusName, jc.setupState) "
					+ " from JoMooCard jc, JoMooCustomer jcu, JoMooType jt, JoMooSpec js, JoMooStatus jst"
					+ " where jcu.active=1 and jt.active=1 and js.active=1 and jst.active=1"
					+ " and jc.customertype=jcu.customerType"
					+ " and jc.typeId = jt.id "
					+ " and jc.specId = js.id "
					+ " and jst.stateName='SETUPTYPE' "
					+ " and jst.statusValue = jc.setupState");

			if (StringUtils.isNotBlank(strPopName))
			{
				hql.append(" and jc.popName like:popname ");
			}

			if (StringUtils.isNotBlank(strPopPhone))
			{
				hql.append(" and jc.popPhone like:pophone ");
			}

			if (null != iCustomerId && !"".equals(iCustomerId))
			{
				hql.append(" and jc.customertype=:customerid ");
			}

			if (null != iSetupType && !"".equals(iSetupType))
			{
				hql.append(" and jc.setupType=:setuptype ");
			}

			if(null != iSetupState && !"".equals(iSetupState))
			{
				hql.append(" and jc.setupState=:setupstate");
			}

			if (null != iProductType && !"".equals(iProductType))
			{
				hql.append(" and jc.typeId=:typeid");
			}

			if (null != iProductSpec && !"".equals(iProductSpec))
			{
				hql.append(" and jc.specId=:specid");
			}

			if (StringUtils.isNotBlank(strBeginTime))
			{
				hql.append(" and jc.setupTime>=:begintime");
			}

			if (null != strEndTime && !"".equals(strEndTime))
			{
				hql.append(" and jc.setupTime<:endtime");
			}

			Query query = session.createQuery(hql.toString());

			if (StringUtils.isNotBlank(strPopName))
			{
				query.setString("popname", "%" + strPopName + "%");
			}

			if (StringUtils.isNotBlank(strPopPhone))
			{
				query.setString("pophone", "%" + strPopPhone + "%");
			}

			if (null != iCustomerId && !"".equals(iCustomerId))
			{
				query.setInteger("customerid", iCustomerId);
			}

			if (null != iSetupType && !"".equals(iSetupType))
			{
				query.setInteger("setuptype", iSetupType);
			}

			if(null != iSetupState && !"".equals(iSetupState))
			{
				query.setInteger("setupstate", iSetupState);
			}

			if (null != iProductType && !"".equals(iProductType))
			{
				query.setInteger("typeid", iProductType);
			}

			if (null != iProductSpec && !"".equals(iProductSpec))
			{
				query.setInteger("specid", iProductSpec);
			}

			if (null != strBeginTime && !"".equals(strBeginTime))
			{
				query.setDate("begintime", simp.parse(strBeginTime));
			}

			if (null != strEndTime && !"".equals(strEndTime))
			{
				query.setDate("endtime", strEndTime);
			}

			cardList = query.list();
		}
		catch (Exception e)
		{
			e.printStackTrace();
			throw e;
		}
		finally
		{
			if (null != session)
			{
				session.close();
			}
			if (null != sessionFactory)
			{
				sessionFactory.close();
			}
		}
		return cardList;
	}
	
	/**
	 * 查询导出服务卡
	 */
	@Override
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List getServiceCardForExcle(String strPopName, String strPopPhone,
			Integer iCustomerId, Integer iSetupType, Integer iSetupState,
			Integer iProductType, Integer iProductSpec, String strBeginTime,
			Date strEndTime) throws Exception
	{
		Session session = sessionFactory.openSession();
		
		List cardList = null;

		SimpleDateFormat simp = new SimpleDateFormat(Constants.DATE_PATTERN);
		try
		{
			StringBuilder hql = new StringBuilder();
			hql.append(" select new JoMooCard(jc.popName, jc.popPhone, jt.typeName, js.specName, " 
					 + " jc.popAddress, jst.statusName, jc.setupType, jc.setupTime, jc.sale, jc.remark) "
					 + " from JoMooCard jc, JoMooCustomer jcu, JoMooType jt, JoMooSpec js, JoMooStatus jst"
					 + " where jcu.active=1 and jt.active=1 and js.active=1 and jst.active=1"
					 + " and jc.customertype=jcu.customerType"
					 + " and jc.typeId = jt.id "
					 + " and jc.specId = js.id "
					 + " and jst.stateName='SETUPTYPE' "
					 + " and jst.statusValue = jc.setupState");

			if (null != strPopName && !"".equals(strPopName))
			{
				hql.append(" and jc.popName like:popname ");
			}

			if (null != strPopPhone && !"".equals(strPopPhone))
			{
				hql.append(" and jc.popPhone like:pophone ");
			}

			if (null != iCustomerId && !"".equals(iCustomerId))
			{
				hql.append(" and jc.customertype=:customerid ");
			}

			if (null != iSetupType && !"".equals(iSetupType))
			{
				hql.append(" and jc.setupType=:setuptype ");
			}

			if(null != iSetupState && !"".equals(iSetupState))
			{
				hql.append(" and jc.setupState=:setupstate");
			}

			if (null != iProductType && !"".equals(iProductType))
			{
				hql.append(" and jc.typeId=:typeid");
			}

			if (null != iProductSpec && !"".equals(iProductSpec))
			{
				hql.append(" and jc.specId=:specid");
			}

			if (null != strBeginTime && !"".equals(strBeginTime))
			{
				hql.append(" and jc.setupTime>=:begintime");
			}

			if (null != strEndTime && !"".equals(strEndTime))
			{
				hql.append(" and jc.setupTime<:endtime");
			}

			Query query = session.createQuery(hql.toString());

			if (null != strPopName && !"".equals(strPopName))
			{
				query.setString("popname", "%" + strPopName + "%");
			}

			if (null != strPopPhone && !"".equals(strPopPhone))
			{
				query.setString("pophone", "%" + strPopPhone + "%");
			}

			if (null != iCustomerId && !"".equals(iCustomerId))
			{
				query.setInteger("customerid", iCustomerId);
			}

			if (null != iSetupType && !"".equals(iSetupType))
			{
				query.setInteger("setuptype", iSetupType);
			}

			if(null != iSetupState && !"".equals(iSetupState))
			{
				query.setInteger("setupstate", iSetupState);
			}

			if (null != iProductType && !"".equals(iProductType))
			{
				query.setInteger("typeid", iProductType);
			}

			if (null != iProductSpec && !"".equals(iProductSpec))
			{
				query.setInteger("specid", iProductSpec);
			}

			if (null != strBeginTime && !"".equals(strBeginTime))
			{
				query.setDate("begintime", simp.parse(strBeginTime));
			}

			if (null != strEndTime && !"".equals(strEndTime))
			{
				query.setDate("endtime", strEndTime);
			}

			cardList = query.list();
		}
		catch (Exception e)
		{
			e.printStackTrace();
			throw e;
		}
		finally
		{
			if (null != session)
			{
				session.close();
			}
			if (null != sessionFactory)
			{
				sessionFactory.close();
			}
		}
		return cardList;
	}

	/**
	 * 作废服务卡
	 * 
	 * @param iCardId
	 * @return
	 * @throws Exception
	 */
	@Override
	public Integer delServiceCard(Integer iCardId) throws Exception
	{
		Session session = sessionFactory.openSession();
		
		Integer iCount = null;

		try
		{
			StringBuilder hql = new StringBuilder();
			hql.append(" update JoMooCard jc set jc.active=0,jc.remark='服务卡已作废',setupState=999 where jc.id=:id ");

			Query query = session.createQuery(hql.toString());

			query.setInteger("id", iCardId);

			iCount = query.executeUpdate();
		}
		catch (Exception e)
		{
			e.printStackTrace();
			throw e;
		}
		finally
		{
			if (null != session)
			{
				session.close();
			}
			if (null != sessionFactory)
			{
				sessionFactory.close();
			}
		}
		return iCount;
	}
	
	/**
	 * 作废服务卡同时金额改为不计入结算
	 * 
	 * @param iCardId
	 * @return
	 * @throws Exception
	 */
	@Override
	public Integer delServiceCardAccount(Integer iCardId) throws Exception
	{
		Session session = sessionFactory.openSession();
		
		Integer iCount = null;

		try
		{
			StringBuilder hql = new StringBuilder();
			hql.append(" update JomooAccount ja set ja.remark='因作废卡单改为不计入结算状态',ja.isBalance=10 where ja.cardId=:id ");

			Query query = session.createQuery(hql.toString());

			query.setInteger("id", iCardId);

			iCount = query.executeUpdate();
			
			session.flush();
		}
		catch (Exception e)
		{
			e.printStackTrace();
			throw e;
		}
		finally
		{
			if (null != session)
			{
				session.close();
			}
			if (null != sessionFactory)
			{
				sessionFactory.close();
			}
		}
		return iCount;
	}
	
	/**
	 * 作废服务卡前查询是否有财务信息存在
	 * 
	 * @param iCardId
	 * @return
	 * @throws Exception
	 */
	@Override
	public Integer getAccountByCardId(Integer iCardId) throws Exception
	{
		Session session = sessionFactory.openSession();
		
		Integer iCount = null;

		try
		{
			StringBuilder hql = new StringBuilder();
			
			hql.append(" select count(*) from JomooAccount ja where ja.cardId=:id ");

			Query query = session.createQuery(hql.toString());

			query.setInteger("id", iCardId);

			Number num = (Number) query.uniqueResult();
			
			iCount = num.intValue();
			
		}
		catch (Exception e)
		{
			e.printStackTrace();
			throw e;
		}
		finally
		{
			if (null != session)
			{
				session.close();
			}
			if (null != sessionFactory)
			{
				sessionFactory.close();
			}
		}
		return iCount;
	}
	
	/**
	 * 变更服务卡状态
	 * @param iCardId
	 * @param iState
	 * @return
	 * @throws Exception
	 */
	@Override
	public Integer updateCardState(Integer iCardId, Integer iState, String strRemark, Date date) throws Exception
	{
		Session session = sessionFactory.openSession();
		
		Integer iCount = null;

		try
		{
			StringBuilder hql = new StringBuilder();
			
			hql.append(" update JoMooCard jc set " +
					 " jc.setupState=:state,jc.remark=:remark ");
			
			if(date != null && Constants.ISTATUS.equals(iState))
			{
				hql.append(" ,jc.lazyTime=:date,jc.isLazy=1 ");
			}
			
			hql.append(" where jc.active=1 and jc.id=:id ");

			Query query = session.createQuery(hql.toString());

			query.setInteger("state", iState);
			query.setString("remark", strRemark);
			
			if(date != null && Constants.ISTATUS.equals(iState))
			{
				query.setTimestamp("date", date);
			}
			
			query.setInteger("id", iCardId);
			
			iCount = query.executeUpdate();
			
			session.flush();
		}
		catch (Exception e)
		{
			e.printStackTrace();
			throw e;
		}
		finally
		{
			if (null != session)
			{
				session.close();
			}
			if (null != sessionFactory)
			{
				sessionFactory.close();
			}
		}
		return iCount;
	}
	
	
	/**
	 * 服务卡详情
	 * @param iCardId
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<JoMooCard> getCardViews(Integer iCardId) throws Exception
	{
		Session session = sessionFactory.openSession();
		
		List<JoMooCard> cardList = null;
		
		try
		{
			StringBuilder hql = new StringBuilder();
			
			hql.append(" select new JoMooCard(jc.popName, jc.popPhone, jt.typeName, js.specName, " +
					 " jc.popAddress, jr.repairName, jc.setupTime, jc.sale, jst.statusName, jc.isLazy, jc.lazyTime) " +
					 " from JoMooCard jc,JoMooType jt,JoMooSpec js,JoMooStatus jst,JoMooRepair jr  " +
					 " where " +
					 " js.active=1 and jt.active=1 and js.active=1 and jst.active=1" +
					 " and jc.typeId=jt.id " +
					 " and jc.specId=js.id " +
					 " and jst.stateName='ISCARD'" +
					 " and jc.isCard=jst.statusValue" +
					 " and jc.setupType=jr.id " +
					 " and jc.id=:id ");

			Query query = session.createQuery(hql.toString());

			query.setInteger("id", iCardId);

			cardList = query.list();
		}
		catch (Exception e)
		{
			e.printStackTrace();
			throw e;
		}
		finally
		{
			if (null != session)
			{
				session.close();
			}
			if (null != sessionFactory)
			{
				sessionFactory.close();
			}
		}
		return cardList;
	}
}